package com.wlc.conn;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import com.wlc.bean.User;

public class UserTableOperation {
	
	public List<User> queryAll()
	{
		String sql="select * from users";
		Connection connection=DBConnection.getConnection();
		List<User> list=new ArrayList<>();
		try {
			PreparedStatement psmt=connection.prepareStatement(sql);
			ResultSet rs=psmt.executeQuery();
			while(rs.next())
			{
				User user=new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public User queryById(int id)
	{
		String sql="select * from users where id=?";
		Connection connection=DBConnection.getConnection();
		User user=null;
		try {
			PreparedStatement psmt=connection.prepareStatement(sql);
			psmt.setInt(1, id);
			ResultSet rs=psmt.executeQuery();
			if(rs.next())
			{
				user=new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
				
			}
		} catch (SQLException e) {
			System.err.println(UserTableOperation.class.getName()+"query By Id method error,message is"+e.getMessage());
		}
		return user;
	}
	public List<User> queryByName(String name)
	{
		String sql="select * from users where name=?";
		Connection connection=DBConnection.getConnection();
		List<User> list=new ArrayList<>();
		try {
			PreparedStatement psmt=connection.prepareStatement(sql);
			psmt.setString(1, name);
			ResultSet rs=psmt.executeQuery();
			while(rs.next())
			{
				User user=new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
				list.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public int addUser(User user) {
		int rowNum=0;
		String sql="insert into users(id,name,password,sex) values(users_id.nextval,?,?,?)";
		Connection connection=DBConnection.getConnection();
		try {
			PreparedStatement psmt=connection.prepareStatement(sql);
			psmt.setString(1, user.getName());
			psmt.setString(2, user.getPassword());
			psmt.setString(3, user.getSex());
			rowNum=psmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println(UserTableOperation.class.getName()+": add user method error,message is "+e.getMessage());
		}
		return rowNum;
	}
	public  int updateUser(User user) {
		Connection connection=DBConnection.getConnection();
	    int i = 0;
	    String sql = "update users set password='" + user.getPassword() + "' where Name='" + user.getName() + "'";
	    try {
	    	PreparedStatement psmt=connection.prepareStatement(sql);
	        i = psmt.executeUpdate();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return i;
	}
	public static int deleteUser(String name) {
		Connection connection=DBConnection.getConnection();
	    int i = 0;
	    String sql = "delete from users where Name='" + name + "'";
	    PreparedStatement pstmt;
	    try {
	    	PreparedStatement psmt=connection.prepareStatement(sql);
	        i = psmt.executeUpdate();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return i;
	}
}
